﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Transactions;
using BDSync.Domains.Entities;
using Dapper;
using DB.SchemaSync.Library;
using DB.SchemaSync.Library.Interfaces;
using DB.SchemaSync.Library.Models;
using DB.SchemaSync.SqlServer;
using DBSync.Tools;
using Newtonsoft.Json;
using Serilog;

namespace BDSync.Domains.DBSnapShot
{
    public class DBCompareServices : IDBCompareServices
    {

        public DBCompareServices()
        {
        }

        private String mDbTarConnStr;

        public String DbTarConnStr
        {
            get { return mDbTarConnStr; }
            set
            {
                mDbTarConnStr = value;
                DbTarProvider = new SqlServerDbProvider(mDbTarConnStr);
                CompareObj = null;
            }
        }

        public IDbConnectionProvider DbTarProvider { get; private set; }
        private SchemaComparison CompareObj;


        /// <summary>
        /// 获取数据库元数据
        /// </summary>
        /// <returns></returns>
        public async Task<Database> GetDataBase()
        {
            try
            {
                if (!CheckConn())
                {
                    throw new ArgumentException("无法连接数据库");
                }

                return await DbTarProvider.GetDatabaseAsync();

            }
            catch (Exception e)
            {
                Log.Logger.Error(e, "扫描数据库失败");
                throw e;
            }
        }


        /// <summary>
        /// 检查连接
        /// </summary>
        /// <returns></returns>
        public bool CheckConn()
        {
            try
            {
                if (string.IsNullOrWhiteSpace(DbTarConnStr))
                {
                    throw new ArgumentException("连接参数为空");
                }

                using (var cn = DBFactory.GetCustomConn(DbTarConnStr))
                {
                    cn.Open();
                    return cn.State == ConnectionState.Open;
                }
            }
            catch (Exception e)
            {
                Log.Logger.Error(e, "数据库连接检查失败");
                return false;
            }

        }


        /// <summary>
        /// 执行比较
        /// </summary>
        /// <param name="dbJsonFile"></param>
        /// <returns></returns>
        public async Task<(IEnumerable<DbObject> Create, IEnumerable<AlteredObject> Alter, IEnumerable<DbObject> Drop)>
            ExceCompare(string dbJsonFile)
        {
            try
            {
                var tardb = await GetDataBase();

                DatabaseReserveObj jsonDB = null;
                using (var fs = new StreamReader(File.Open(dbJsonFile, FileMode.Open)))
                {
                    var jsoncontent = fs.ReadToEnd();
                    jsonDB = JsonConvert.DeserializeObject<DatabaseReserveObj>(jsoncontent);
                    fs.Close();
                }

                var srcdb = jsonDB.DB;
                srcdb.RepairReference();

                CompareObj = new SchemaComparison(srcdb, tardb);
                CompareObj.Execute();

                return (CompareObj.Create, CompareObj.Alter, CompareObj.Drop);
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }

        }


        /// <summary>
        /// 执行比较 
        /// </summary>
        /// <param name="dbJsonFile"></param>
        /// <returns></returns>
        public IEnumerable<string> ExportSqlForSelection()
        {
            if (CompareObj == null)
            {
                throw new ApplicationException("必须是先执行比较");
            }

            return CompareObj.GetScriptCommandsCheckSelected(new SqlServerSyntax());
        }

        /// <summary>
        /// 执行比较 
        /// </summary>
        /// <param name="dbJsonFile"></param>
        /// <returns></returns>
        public IEnumerable<string> ExportSql()
        {
            if (CompareObj == null)
            {
                throw new ApplicationException("必须是先执行比较");
            }

            return CompareObj.GetScriptCommands(new SqlServerSyntax());
        }


        /// <summary>
        /// 执行比较 
        /// </summary>
        /// <param name="dbJsonFile"></param>
        /// <returns></returns>
        public void ExportSqlFileForSelection(string filepath)
        {
            if (CompareObj == null)
            {
                throw new ApplicationException("必须是先执行比较");
            }

            var res = CompareObj.GetScriptCommandsCheckSelected(new SqlServerSyntax());
            using (var sw = new StreamWriter(new FileStream(filepath, FileMode.Create)))
            {
                foreach (var li in res)
                {
                    sw.WriteLine(li);
                }

                sw.Flush();
            }

        }

        /// <summary>
        /// 执行比较 
        /// </summary>
        /// <param name="dbJsonFile"></param>
        /// <returns></returns>
        public void ExportSqlFile(string filepath)
        {
            if (CompareObj == null)
            {
                throw new ApplicationException("必须是先执行比较");
            }

            var res = CompareObj.GetScriptCommands(new SqlServerSyntax());
            using (var sw = new StreamWriter(new FileStream(filepath, FileMode.Create)))
            {
                foreach (var li in res)
                {
                    sw.WriteLine(li);
                }

                sw.Flush();
            }
        }


        /// <summary>
        /// 执行差异sql
        /// </summary>
        /// <returns>执行不成功SQL会直接返回</returns>
        public string ExecSql()
        {
            var outlet = new StringBuilder();

            if (CompareObj == null)
            {
                throw new ApplicationException("必须是先执行比较");
            }

            if (!CheckConn())
            {
                throw new ApplicationException("无法连接数据库");
            }

            var synt = new SqlServerSyntax();
            var sqls = ExportSql();
            var sqlSkimSeparators = sqls.Where(x => x != synt.BatchSeparator);

            using (var tran = new TransactionScope(TransactionScopeOption.Required,TransactionScopeAsyncFlowOption.Enabled) )
            {
                using (var conn = DBFactory.GetCustomConn(DbTarConnStr))
                {
                    foreach (var sqlitem in sqlSkimSeparators)
                    {
                        //conn.Execute(sqlitem);
                        try
                        {
                            conn.Execute(sqlitem);

                        }
                        catch (Exception e)
                        {
                            outlet.AppendLine(sqlitem);
                            Log.Logger.Error(@$"执行比较报错：error【{e}】 sql【{sqlitem}】");
                        }
                    }
                    tran.Complete();
                }
            }

            return outlet.ToString();

        }


        /// <summary>
        /// 执行所选差异sql
        /// </summary>
        public string ExecSqlForSelection()
        {
            var outlet = new StringBuilder();

            if (CompareObj == null)
            {
                throw new ApplicationException("必须是先执行比较");
            }

            if (!CheckConn())
            {
                throw new ApplicationException("无法连接数据库");
            }

            var synt = new SqlServerSyntax();
            var sqls = ExportSqlForSelection();
            var sqlSkimSeparators = sqls.Where(x => x != synt.BatchSeparator);

            using (var tran = new TransactionScope(TransactionScopeOption.Required, TransactionScopeAsyncFlowOption.Enabled))
            {
                using (var conn = DBFactory.GetCustomConn(DbTarConnStr))
                {
                    foreach (var sqlitem in sqlSkimSeparators)
                    {
                        //conn.Execute(sqlitem);

                        try
                        {
                            conn.Execute(sqlitem);

                        }
                        catch (Exception e)
                        {
                            outlet.AppendLine(sqlitem);
                            Log.Logger.Error(@$"执行比较报错：error【{e}】 sql【{sqlitem}】");
                        }
                    }

                    tran.Complete();
                }
            }

            return outlet.ToString();
        }

    }
}
